 //
//  BIDTypesManager.m
//  随手记01
//
//  Created by zd2011 on 13-5-23.
//  Copyright (c) 2013年 邵瑞. All rights reserved.
//

#import "BIDTypesManager.h"
#import "FMDatabase.h"
#import "FMDatabaseAdditions.h"
#import "BIDABill.h"
#import "BIDDatabaseManager.h"
#import "BIDBillsLookoverManager.h"
#define kTypeTable @"typeTableInfo"//主表
#define kSubTypeTable @"subTypeInfo"//子表
#define kTableName @"MessageInfoTable"//保存用户输入数据的表

#define kIsReadDatabase @"isReadDatabase003"
@interface BIDTypesManager()
{
    FMDatabase *aDatabase;
    BOOL isReadDatabase;
}
@end  
@implementation BIDTypesManager
+(BIDTypesManager *)shareTypesManager
{
    static BIDTypesManager *typeManage=nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        typeManage=[[BIDTypesManager alloc] init];
    });
    return typeManage;
}
-(id)init
{
    aDatabase=[BIDDatabaseManager SharedOperator].fmdbDataBase;
    if (![aDatabase open]) {
        NSLog(@"false open");
    }
    if (![[[NSUserDefaults standardUserDefaults] objectForKey:kIsReadDatabase] boolValue]) {
        [self readPlistInfoToDatabase];
    }
        return self;
}
-(BOOL)insertATypeOrASubType:(NSString *)type orSubtype:(NSString *)subType
{
    BOOL judge=NO;
    if (type&&!subType) {
         NSString *typeInsert=[NSString stringWithFormat:@"insert into %@(typename) values('%@')",kTypeTable,type];
        judge=[aDatabase executeUpdate:typeInsert];
    }
    if (type&&subType) {
        NSString *typeIdSelect=[NSString stringWithFormat:@"select typeInfo_ID from %@ where typename='%@'",kTypeTable,type];
        int typeId=[aDatabase intForQuery:typeIdSelect];
        
        
        NSString *subtypeIns=[NSString stringWithFormat:@"insert into %@(subTypename,refer_ID) values('%@','%d')",kSubTypeTable,subType,typeId];
        judge=[aDatabase executeUpdate:subtypeIns];
    }
    
    
    return judge;
    
}
-(BOOL)readPlistInfoToDatabase
{
    BOOL judge=NO;
    NSBundle *bundle=[NSBundle mainBundle];
    NSURL *plistURL=[bundle URLForResource:@"types"
                             withExtension:@"plist"];
    NSDictionary *dictionary=[NSDictionary
                              dictionaryWithContentsOfURL:plistURL];
    
    NSArray *arr=[dictionary allKeys];
    for (int i=0; i<arr.count; i++)
    {
        NSString *aType=[arr objectAtIndex:i];
        BOOL judegr=[self insertATypeOrASubType:aType orSubtype:nil];
        if (judegr)
        {
            judge=YES;
            // NSLog(@"success");
        }
        NSArray *subType=[dictionary objectForKey:aType];
        for (int j=0; j<subType.count; j++) {
            NSString *string=[subType objectAtIndex:j];
            BOOL jud=[self insertATypeOrASubType:aType orSubtype:string];
            if (jud) {
                judge=YES;
                //  NSLog(@"subSuccess  ");
            }
            
        }
    }
    
    
    isReadDatabase=YES;
    [[NSUserDefaults standardUserDefaults] setValue:[NSNumber numberWithBool:isReadDatabase] forKey:kIsReadDatabase];
    [[NSUserDefaults standardUserDefaults] synchronize];
    
    return YES;
    
}
-(NSMutableArray *)DatabaseHadTypes
{
    NSMutableArray *array=[[NSMutableArray alloc] init];
    NSString *str=[NSString stringWithFormat:@"select typeInfo_ID from %@ ",kTypeTable];
    FMResultSet *result=[aDatabase executeQuery:str];
    while ([result next]) {
        NSInteger type_id=[result intForColumn:@"typeInfo_ID"];
        [array addObject:[NSNumber numberWithInteger:type_id]];
    
    }
    return array;
    [array release];
}
-(NSInteger)numberOfSubTypeInType:(NSInteger)type_id
{
    NSInteger number=0;
    NSString *str=[NSString stringWithFormat:@"select count(*) from %@ where refer_ID=%d",kSubTypeTable,type_id];
    number=[aDatabase intForQuery:str];
    return number;
}
-(NSMutableArray *)listOfSubTypeInMainType:(NSInteger)mainType_id
{
    NSMutableArray *array=[[NSMutableArray alloc] init];
    NSString *str=[NSString stringWithFormat:@"select * from %@ where refer_ID=%d",kSubTypeTable,mainType_id];
    FMResultSet *end=[aDatabase executeQuery:str];
    while ([end next]) {
        BIDATypes *aType=[[BIDATypes alloc] init];
        aType.typeId=mainType_id;
        aType.subTypeId=[end intForColumn:@"subclassInfo_ID"];
        [array addObject:aType];
        [aType release];
    }
    return array;
    [array release];
}
-(NSMutableDictionary *)readDatabasePutIntoDict
{
    NSMutableDictionary *dict=[[NSMutableDictionary alloc] init];
    
    NSMutableArray *typeArr=[self DatabaseHadTypes];
    for (int i=0; i<typeArr.count;i++) {
        NSMutableArray *aSubTypeArr=[[NSMutableArray alloc] init];
        NSString *aType=[typeArr objectAtIndex:i];
        NSString *str=[NSString stringWithFormat:@"select * from %@ a,%@ b where a.refer_ID=b.typeInfo_ID and b.typename='%@'",kSubTypeTable,kTypeTable,aType];
        FMResultSet *ads=[aDatabase executeQuery:str];
        while ([ads next]) {
            NSString *aSubType=[ads stringForColumn:@"subTypename"];
            [aSubTypeArr addObject:aSubType];
        }
        
        [dict setValue:aSubTypeArr forKey:aType];
        [aSubTypeArr release];
        
    }
    return dict;
    [dict release];
    
}
-(BOOL)updataTheTypeOrSubType:(BIDATypes *)nowType andBeforeType:(BIDATypes *)oldType
{
    BOOL judge=NO;
    if (![nowType.type isEqualToString:oldType.type] &&[nowType.subType isEqualToString:oldType.subType])
    {
        NSString *updataType=[NSString stringWithFormat:@"update %@ set typename='%@' where typeInfo_ID=%d",kTypeTable,nowType.type,oldType.typeId];
        judge=[aDatabase executeUpdate:updataType];
        
    }
    if ([nowType.type isEqualToString:oldType.type] &&![nowType.subType isEqualToString:oldType.subType])
    {
        NSString *updataSubType=[NSString stringWithFormat:@"update %@ set subTypename='%@' where subclassInfo_ID=%d",kSubTypeTable,nowType.subType,oldType.subTypeId];
        judge=[aDatabase executeUpdate:updataSubType];
    }
    if (![nowType.type isEqualToString:oldType.type] &&![nowType.subType isEqualToString:oldType.subType])
    {
       NSString *updataType=[NSString stringWithFormat:@"update %@ set typename='%@' where typeInfo_ID=%d",kTypeTable,nowType.type,oldType.typeId];
        judge=[aDatabase executeUpdate:updataType];
       NSString *updataSubType=[NSString stringWithFormat:@"update %@ set subTypename='%@' where subclassInfo_ID=%d",kSubTypeTable,nowType.subType,oldType.subTypeId];
        judge=[aDatabase executeUpdate:updataSubType];
        


    }

        return judge;
}
-(BOOL)addTypeOSubType:(BIDATypes *)subtypeOrType addAtypeOrSubType:(NSString *)type OrSubType:(NSString *)subType
{
    BOOL judge=NO;
    if (subtypeOrType==nil && type== nil && subType ==nil)
       {
                     NSLog(@"都为空");
              UIAlertView * alert = [[UIAlertView alloc] initWithTitle:@"注意" message:@"请输入类别:" delegate:self cancelButtonTitle:@"确认" otherButtonTitles:nil ];
              [alert show];
              [alert release];
       }
       else
       {
              if (type&&!subType)
                   {
//                      NSString *insertType=[NSString stringWithFormat:@"insert into %@(typename) values('%@')",kTypeTable,type];
//                      judge=[aDatabase executeUpdate:insertType];
                  }
              if (![subtypeOrType.type isEqualToString:type] &&![subtypeOrType.subType isEqualToString:subType])
              {
              
                   
              NSString * inserType = [NSString stringWithFormat:@"insert into %@(typename) values ('%@')",kTypeTable,type];
              judge=[aDatabase executeUpdate:inserType];

              }
              if ([subtypeOrType.type isEqualToString:type] &&![subtypeOrType.subType isEqualToString:subType])
                  {
                   NSString *selectType_Id=[NSString stringWithFormat:@"select typeInfo_ID from %@ where typename='%@' ",kTypeTable,subtypeOrType.type];
                      NSInteger typeID=[aDatabase intForQuery:selectType_Id];
                   NSString *insertSubType=[NSString stringWithFormat:@"insert into %@(subTypename,refer_ID) values('%@','%d')",kSubTypeTable,subType,typeID];
                      judge=[aDatabase executeUpdate:insertSubType];
                  }
                 
       }
    

    return judge;
}

-(BOOL)addAtypeOrSubType:(NSString *)type OrSubType:(NSString *)subType
{
    BOOL judge=NO;
//    if (type&&!subType)
//     {
//        NSString *insertType=[NSString stringWithFormat:@"insert into %@(typename) values('%@')",kTypeTable,type];
//        judge=[aDatabase executeUpdate:insertType];
//    }
//    
//    if (type&&subType)
//    {
//        NSString *selectType_Id=[NSString stringWithFormat:@"select typeInfo_ID from %@ where typename='%@' ",kTypeTable,type];
//        NSInteger typeID=[aDatabase intForQuery:selectType_Id];
//        NSString *insertSubType=[NSString stringWithFormat:@"insert into %@(subTypename,refer_ID) values('%@','%d')",kSubTypeTable,subType,typeID];
//        judge=[aDatabase executeUpdate:insertSubType];
//    }

    return judge;
}
-(NSString *)selectTypeName:(NSInteger)type_id
{
    NSString *typeName;
    NSString *selectTypeName=[NSString stringWithFormat:@"select typename from %@ where typeInfo_ID=%d",kTypeTable,type_id];
    typeName=[aDatabase stringForQuery:selectTypeName];
    return typeName;
}
-(NSString *)selectSubTypeName:(NSInteger)subType_id
{
    NSString *typeName;
    NSString *selectTypeName=[NSString stringWithFormat:@"select subTypename from %@ where subclassInfo_ID=%d",kSubTypeTable,subType_id];
    typeName=[aDatabase stringForQuery:selectTypeName];
    return typeName;
    
}
-(NSInteger)selectTypeid:(NSString *)typeName
{
    NSInteger typeid;
    NSString *selectTypeID=[NSString stringWithFormat:@"select typeInfo_ID  from %@ where typename='%@'",kTypeTable,typeName];
    typeid=[aDatabase intForQuery:selectTypeID];
    return typeid;


}
-(NSInteger)selectSubTypeid:(NSString *)subTypeName
{
    
    NSInteger subTypeid ;
    NSString *selectTypeID=[NSString stringWithFormat:@"select subclassInfo_ID from %@ where subTypename='%@'",kSubTypeTable,subTypeName];
    subTypeid=[aDatabase intForQuery:selectTypeID];
    return subTypeid;

}
/////
-(NSMutableArray *)totalYearBills
{
       NSMutableArray * CountYearBills = [[NSMutableArray alloc] init];
        NSString * selectYear = [NSString stringWithFormat:@" select distinct strftime('%%Y',time) year from %@",kTableName];
       FMResultSet *selecYears = [aDatabase executeQuery:selectYear];
       while ([selecYears next])
       {
              NSInteger  aYear = [selecYears intForColumn:@"year"];
              [CountYearBills addObject:[NSNumber numberWithInteger:aYear]];
       }
       
       return CountYearBills;
}
//删除某一年的账单
-(BOOL)deleteAYearBill:(NSInteger)aYear
{
      NSInteger deleteAYear;
      NSString *deleteStr  = [NSString stringWithFormat:@"delete from %@ where strftime('%%Y',time) ='%d'",kTableName,aYear];
      deleteAYear = [aDatabase executeUpdate:deleteStr];
      if (deleteAYear)
       {
              NSLog(@"delete chenggong");
       }
       else
       {
              NSLog(@"delete shibai");
       }

      return YES;
}
//查某一月的账单
-(NSInteger) selectAMonthBills:(NSInteger)aYear
{
       NSInteger selectMonth;
       NSString * strMonth = [NSString stringWithFormat:@"select strftime('%%m',time) from %@ where strftime('%%Y',time)=='%d'",kTableName,aYear];
       selectMonth = [aDatabase intForQuery:strMonth];
       return selectMonth;
}
-(NSMutableArray *)aYearOfMonthTotalBills:(NSInteger)aYear
{
//distinct消除重复
       NSMutableArray * monthBills = [[NSMutableArray alloc] init];
        NSString * strMonth = [NSString stringWithFormat:@"select distinct strftime('%%m',time) month from %@ where strftime('%%Y',time)=='%d'",kTableName,aYear];
       FMResultSet *selectMonth = [aDatabase executeQuery:strMonth];
       while ([selectMonth next])
        {
  
              NSInteger months = [selectMonth intForColumn:@"month"];
              [monthBills addObject:[NSNumber numberWithInteger:months]];
       }
       return monthBills;

       
}
//删除某一个月的账单
-(BOOL)deleteAMonthBill:(NSInteger)aMonth inYear:(NSInteger)year
{
       NSInteger deleteM;
       NSString *useful=[self monthBecomeLawful:aMonth];
       NSString *deleteStrM  = [NSString stringWithFormat:@"delete  from %@ where strftime('%%m',time)=='%@' and strftime('%%Y',time)=='%d' ",kTableName,useful,year];
      deleteM = [aDatabase executeUpdate:deleteStrM];
      if (deleteM)
       {
              NSLog(@"delete chenggong");
       }
       else
       {
              NSLog(@"delete shibai");
       }
      return YES;
}

-(NSString*)monthBecomeLawful:(NSInteger)month
{
    NSString *str;
    if (month<=9) {
        str = [NSString stringWithFormat:@"0%d",month];
        return str;
    }
    else
        str=[NSString stringWithFormat:@"%d",month];
        return str;
}

@end
